package saucaca.Database;

import java.util.ArrayList;

import saucaca.Object.SongObject;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;


public class DataBase {
	private static final String DB_NAME = "saucaca.DataBase";
	private static final String TABLE_MY_FAV = "myfav";
	// private static final String TABLE_PLAY_LIST = "myplaylist";
	// private static final String TABLE_MUSIC_IN_PLAY_LIST = "mylistmusic";
	private static final String TABLE_PLAYLIST = "playlist";
	private static final String TABLE_MYLIST_PREFIX = "myplaylist";
	private Context context;

	public DataBase(Context _context) {

		this.context = _context;
		create();
	}
//	public String SEO_NAME;
//	public String SONG_ID;
//	public String TXNAME;
//	public String SONG_CODE;
//	public String AVATAR;
//	public String AVATAR_SINGER;
//	public String NAME_SINGERS;
//	public String ID_SINGERS;
//	public String ISVIEW;
//	public String ISDOWN;
//	public String RN;
//	private String ALBUM;
//	private String PATCH_FULL;
//	private String LYRCIS;
//	private String SHORTDESC;
//	private String TYPE_VIDEO;
//	private String ISLIKE;
//	private long Size;
//	private long duration;
//	private boolean FROMNET;
//	private String ISTONE;
//	private String TXURL;
	private void create() {
		SQLiteDatabase db = context.openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null);
//		/*
//		 * db.execSQL("DROP TABLE IF EXISTS "+TABLE_ONLINE_MUSIC);
//		 * db.execSQL("DROP TABLE IF EXISTS "+TABLE_MY_FAV);
//		 * db.execSQL("DROP TABLE IF EXISTS "+TABLE_PLAYLIST);
//		 */
		db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_MY_FAV
				+ " (uid INTEGER PRIMARY KEY AUTOINCREMENT, songid VARCHAR, songname VARCHAR, songcode VARCHAR, pic VARCHAR, sname VARCHAR, sid VARCHAR, isview VARCHAR,"
				+ " isdown VARCHAR, rn VARCHAR,album VARCHAR, fileurl VARCHAR NOT NULL UNIQUE,lrcurl VARCHAR, fromnet VARCHAR)");
		
		db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_PLAYLIST + " (uid INTEGER PRIMARY KEY AUTOINCREMENT, listtable VARCHAR, listname VARCHAR)");
		db.close();
	}

	public boolean addFav(SongObject music) {
		ArrayList<SongObject> list = getFav();
		for (int i = 0; i < list.size(); i++) {
			if (list.get(i).getPATCH_FULL().equals(music.getPATCH_FULL()))
				return false;
		}
		SQLiteDatabase db = context.openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null);
		try {
			ContentValues values = new ContentValues();
			values.putAll(new MusicBuilder().deconstruct(music));
			db.insert(TABLE_MY_FAV, null, values);
			db.close();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	public ArrayList<SongObject> getFav() {
		ArrayList<SongObject> fav = new ArrayList<SongObject>();
		MusicBuilder builder = new MusicBuilder();
		SQLiteDatabase db = context.openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null);
		Cursor query = db.query(TABLE_MY_FAV, null, null, null, null, null, null);
		if (query != null) {
			query.moveToFirst();
			while (!query.isAfterLast()) {
				fav.add(builder.build(query));
				query.moveToNext();
			}
		}
		query.close();
		db.close();
		return fav;
	}

	public boolean delFav(SongObject music) {
		SQLiteDatabase db = context.openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null);
		String sql = "delete from " + TABLE_MY_FAV + " where fileurl='" + music.getPATCH_FULL() + "'";
		try {
			db.execSQL(sql);
			db.close();
			return true;
		} catch (Exception ex) {
			db.close();
			return false;
		}
	}

	public boolean creatNewPlayList(String listname) {
		SQLiteDatabase db = context.openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null);
		int node = 1;
		Cursor query = db.query(TABLE_PLAYLIST, null, null, null, null, null, null);
		if (query != null) {
			query.moveToFirst();
			while (!query.isAfterLast()) {
				if (listname.equals(query.getString(query.getColumnIndex("listname"))))
					return false;
				if (query.getString(query.getColumnIndex("listtable")).equals(TABLE_MYLIST_PREFIX + node))
					node++;
				query.moveToNext();
			}
		}
		try {
			// db.beginTransaction();
			// create new playlist table to store music
			String table_name = TABLE_MYLIST_PREFIX + (node);
			db.execSQL("CREATE TABLE IF NOT EXISTS " + table_name
					+ " (uid INTEGER PRIMARY KEY AUTOINCREMENT, songid VARCHAR, songname VARCHAR, songcode VARCHAR, pic VARCHAR, sname VARCHAR, sid VARCHAR, isview VARCHAR,"
					+ " isdown VARCHAR, rn VARCHAR,album VARCHAR, fileurl VARCHAR NOT NULL UNIQUE,lrcurl VARCHAR, fromnet VARCHAR)");
			// add the new playlist table to the table list
			db.execSQL("insert into " + TABLE_PLAYLIST + " (listtable, listname) values ('" + table_name + "', '" + listname + "')");
			// db.endTransaction();
			query.close();
			db.close();
			return true;
		} catch (Exception ex) {
			query.close();
			db.close();
			return false;
		}
	}

	public ArrayList<String> getMyPlayList() {
		ArrayList<String> playlistnamelist = new ArrayList<String>();
		SQLiteDatabase db = context.openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null);
		Cursor query = db.query(TABLE_PLAYLIST, null, null, null, null, null, null);
		if (query != null) {
			query.moveToFirst();
			while (!query.isAfterLast()) {
				playlistnamelist.add(query.getString(query.getColumnIndex("listname")));
				query.moveToNext();
			}
		}
		query.close();
		db.close();
		return playlistnamelist;
	}

	public boolean delMyPlayList(String listname) {
		SQLiteDatabase db = context.openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null);
		Cursor query = db.query(TABLE_PLAYLIST, null, null, null, null, null, null);
		String table_name = "";
		if (query != null) {
			query.moveToFirst();
			while (!query.isAfterLast()) {
				if (listname.equals(query.getString(query.getColumnIndex("listname")))) {
					table_name = query.getString(query.getColumnIndex("listtable"));
					break;
				}
				query.moveToNext();
			}
		}
		query.close();
		if (table_name == null || table_name.equals("")) {
			db.close();
			return false;
		}

		try {
			db.execSQL("DROP TABLE IF EXISTS " + table_name);
			db.execSQL("delete from " + TABLE_PLAYLIST + " where listname='" + listname + "'");
			db.close();
			return true;
		} catch (Exception ex) {
			db.close();
			return false;
		}
	}

	public boolean addMusicToPlayList(ArrayList<SongObject> musiclist, String listname) {
		SQLiteDatabase db = context.openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null);
		Cursor query = db.query(TABLE_PLAYLIST, null, null, null, null, null, null);
		String table_name = "";
		if (query != null) {
			query.moveToFirst();
			while (!query.isAfterLast()) {
				if (listname.equals(query.getString(query.getColumnIndex("listname")))) {
					table_name = query.getString(query.getColumnIndex("listtable"));
					break;
				}
				query.moveToNext();
			}
		}
		query.close();

		// ArrayList<SongObject> data=getMusicInPlayList(listname);
		try {
			for (SongObject music : musiclist) {
				// boolean insert=true;
				// judge the music is already in the list?
				/*
				 * for(Music m : data){
				 * if(m.getWebFile().equals(music.getWebFile())){ insert=false;
				 * break; } }
				 */
				// if(insert){

				ContentValues values = new ContentValues();
				values.putAll(new MusicBuilder().deconstruct(music));
				db.insert(table_name, null, values);

				// }
			}
			db.close();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	public ArrayList<SongObject> getMusicInPlayList(String listname) {
		SQLiteDatabase db = context.openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null);
		String table_name = "";
		Cursor query = db.query(TABLE_PLAYLIST, null, null, null, null, null, null);
		if (query != null) {
			query.moveToFirst();
			while (!query.isAfterLast()) {
				if (listname.equals(query.getString(query.getColumnIndex("listname")))) {
					table_name = query.getString(query.getColumnIndex("listtable"));
					break;
				}
				query.moveToNext();
			}
		}
		query.close();
		if (table_name == null || table_name.equals("")) {
			db.close();
			return null;
		}
		// get music from playlist
		ArrayList<SongObject> music = new ArrayList<SongObject>();
		query = db.query(table_name, null, null, null, null, null, null);
		if (query != null) {
			query.moveToFirst();
			while (!query.isAfterLast()) {
				music.add(new MusicBuilder().build(query));
				query.moveToNext();
			}
		}
		query.close();
		db.close();
		return music;
	}

	public boolean delMusicInPlayList(ArrayList<SongObject> musiclist, String listname) {
		SQLiteDatabase db = context.openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null);
		Cursor query = db.query(TABLE_PLAYLIST, null, null, null, null, null, null);
		String table_name = "";
		if (query != null) {
			query.moveToFirst();
			while (!query.isAfterLast()) {
				if (listname.equals(query.getString(query.getColumnIndex("listname")))) {
					table_name = query.getString(query.getColumnIndex("listtable"));
					break;
				}
				query.moveToNext();
			}
		}
		query.close();
		try {
			for (SongObject music : musiclist) {
				String sql = "delete from " + table_name + " where fileurl='" + music.getPATCH_FULL() + "'";
				db.execSQL(sql);
			}
			db.close();
			return true;
		} catch (Exception ex) {
			db.close();
			return false;
		}
	}
}
